package DAO.MySQL;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

import DAO.DAOException;
import DAO.EmployeeDAO;
import Model.EmployeeBean;

public class EmployeeDAOImpl
	extends GenericDAO<EmployeeBean>
	implements EmployeeDAO
{
	private static final String TARGET_TABLE = "employee_tb";
	
	@Override
	protected EmployeeBean parseResultSet(ResultSet rs) throws SQLException {
		EmployeeBean emp = new EmployeeBean();
    	emp.setEmployeeId(rs.getInt(1));
    	emp.setManagerId(rs.getInt(2));
    	emp.setIsAdmin(rs.getBoolean(3));
    	emp.setRankID(rs.getInt(4));
    	emp.setName(rs.getString(5));
    	emp.setPassword(rs.getString(6));
    	emp.setEmail(rs.getString(7));
    	emp.setCreatedDate(rs.getDate(8));
    	emp.setModifiedDate(rs.getDate(9));
    	emp.setDeleteFlag(rs.getBoolean(10));
		return emp;
	}

	@Override
	public ArrayList<EmployeeBean> findAllNotDeleteEmployee()
		throws ClassNotFoundException, SQLException	
	{ return ( findEmployeeWith(" where delete_flag='0' ") ); }
	
	@Override
	public ArrayList<EmployeeBean> findAllDeletedEmployee()
		throws ClassNotFoundException, SQLException
	{ return ( findEmployeeWith(" where delete_flag='1' ") ); }
	
	public ArrayList<EmployeeBean> findAllEmployeeIgnoreDelete()
		throws ClassNotFoundException, SQLException
	{ return ( findEmployeeWith("") ); }
	
	@Override
	public ArrayList<EmployeeBean> findEmployeeWith(String condition)
		throws ClassNotFoundException, SQLException
	{
		String query = "select *  from " + TARGET_TABLE + " " + condition;
		//System.out.println(query);
		return ( executeMultipleQuery(query) );
	}
	@Override
	public EmployeeBean findEmployeeById(int inEmpID)
		throws ClassNotFoundException, SQLException
	{
		ArrayList<EmployeeBean> eList= findEmployeeWith(" where employee_id='" + inEmpID + "'");
		if (eList.size() == 1)
		{ return eList.get(0); }
		else
		{ return null; }
	}
	@Override
	public ArrayList<EmployeeBean> findNotDeleteEmployeeByManager(int inManID)
		throws ClassNotFoundException, SQLException
	{
		String query = " where manager_id='" + inManID + "' and delete_flag='0'";
		return findEmployeeWith(query);
	}



	@Override
	public int findNextEmployeeID()
		throws ClassNotFoundException, SQLException
	{
		int output = 1;
		String query = "select max(employee_id) from " + TARGET_TABLE;
		String temp = executeOneCellQuery(query);
		if (temp == null) {		//If no results found
			output = 1;			//return 1
		} else {					//Otherwise results found
			output = ( Integer.parseInt(temp) + 1 );
		}
		return output;
	}

	@Override
	public void addEmployee(EmployeeBean employee)
		throws ClassNotFoundException, SQLException
	{
		//insert into employee_tb values (5,3,0,1,'Name','pass','email','2011-01-01','2011-01-01',1);
		employee.setCreatedDate( new Date( System.currentTimeMillis() ) );	//override dates
		employee.setModifiedDate( new Date( System.currentTimeMillis() ) );	//override dates
		StringBuffer statement = new StringBuffer();
		statement.append("insert into " + TARGET_TABLE + " values (");
		statement.append( employee.getEmployeeId() + "," );
		statement.append( employee.getManagerId() + "," );
		statement.append( BoolToInt(employee.getIsAdmin()) + "," );
		statement.append( employee.getRankID() + "," );
		statement.append( "'" + employee.getName() + "'," );
		statement.append( "'" + employee.getEmployeeId() + "'," );
		statement.append( "'" + employee.getEmail() + "'," );
		statement.append( "'" + employee.getCreatedDate() + "'," );
		statement.append( "'" + employee.getModifiedDate() + "'," );
		statement.append( BoolToInt(employee.getDeleteFlag()) );
		statement.append( ")" );
		//System.out.println(statement);
		executeSingleUpdate( statement.toString() );
	}

	@Override
	public void updateEmployee(EmployeeBean employee)
		throws ClassNotFoundException, SQLException
	{
		employee.setModifiedDate( new Date( System.currentTimeMillis() ) );	//override dates
		StringBuffer statement = new StringBuffer();
		statement.append("update " + TARGET_TABLE + " set ");
		statement.append("manager_id=" + employee.getManagerId() + ", ");
		statement.append("is_admin=" + BoolToInt(employee.getIsAdmin()) + ", ");
		statement.append("rank_id=" + employee.getRankID() + ", ");
		statement.append("name='" + employee.getName() + "', ");
		statement.append("email='" + employee.getEmail() + "', ");
		statement.append("modified_date='" + employee.getModifiedDate() + "', ");
		statement.append("delete_flag=" + BoolToInt(employee.getDeleteFlag()) + " ");
		statement.append( "where employee_id="+employee.getEmployeeId() );
		//System.out.println(statement);
		executeSingleUpdate( statement.toString() );
	}

	private static int BoolToInt(boolean input) {
		if (input)
		{return 1;}
		else
		{return 0;}
	}

	@Override
	public void resetPassword(Integer inEmpID)
		throws ClassNotFoundException, SQLException
	{
		StringBuffer statement = new StringBuffer();
		statement.append("update " + TARGET_TABLE + " set ");
		statement.append("password='" + inEmpID + "', ");
		statement.append("modified_date='" + new Date( System.currentTimeMillis() ) + "' ");
		statement.append( "where employee_id="+inEmpID );
		//System.out.println(statement);
		executeSingleUpdate( statement.toString() );		
	}

	@Override
	public void updatePassword(EmployeeBean employee)
		throws ClassNotFoundException, SQLException
	{
		StringBuffer statement = new StringBuffer();
		statement.append("update " + TARGET_TABLE + " set ");
		statement.append("password='" + employee.getPassword() + "', ");
		statement.append("modified_date='" + new Date( System.currentTimeMillis() ) + "' ");
		statement.append( "where employee_id=" + employee.getEmployeeId() );
		//System.out.println(statement);
		executeSingleUpdate( statement.toString() );	
	}
}
